<?php
class XA_Db_Table_MySQL extends XA_Db_Table {



	public function __construct($table_name) {
		parent::__construct($table_name);

		foreach (xa::query('DESCRIBE `'.TP.$table_name.'`')->all('Field') as $field_name => $field_desc) {

			if (substr_count($field_desc['Key'], 'PRI')) {
				$this->primary_key_list[] = $field_name;
			}

			if ($field_desc['Extra'] === 'auto_increment') {
				$this->auto_increment_field = $field_name;
			}

			$field = array();

			$field['null'] = ($field_desc['Null'] === 'YES');

			if (!empty($field_desc['Default']) || ($field_desc['Default'] === '0')) {
				$field['default'] = $field_desc['Default'];
			}

			$field['unsigned'] = (bool)substr_count($field_desc['Type'], 'unsigned');

			switch (true) {
				case (substr_count($field_desc['Type'], 'char')): {
					$field['type'] = 'char';
					$field['size'] = intval(substr($field_desc['Type'], stripos($field_desc['Type'], '(') + 1, -1));
					break;
				}
				case (substr_count($field_desc['Type'], 'int')): {
					$field['type'] = 'int';
					$field['size'] = intval(substr($field_desc['Type'], stripos($field_desc['Type'], '(') + 1, -1));
					break;
				}
				case ($field_desc['Type'] === 'float'): {
					$field['size'] = 20;
					$field['type'] = 'float';
					break;
				}
				case ($field_desc['Type'] === 'datetime'): {
					$field['type'] = 'datetime';
					break;
				}
				case ($field_desc['Type'] === 'date'): {
					$field['type'] = 'date';
					break;
				}
				case ($field_desc['Type'] === 'time'): {
					$field['type'] = 'time';
					break;
				}
				case ($field_desc['Type'] === 'tinytext'): {
					$field['type'] = 'tinytext';
					break;
				}
				case (substr_count($field_desc['Type'], 'text')): {
					$field['type'] = 'text';
					break;
				}
				case (stripos($field_desc['Type'], 'decimal') === 0): {
					$field['type'] = 'float';
					list($unit_size, $fraction_size) = explode(',', substr($field_desc['Type'], stripos($field_desc['Type'], '(') + 1, - 1));
					$field['size'] = $unit_size + $fraction_size;
					break;
				}
				case (stripos($field_desc['Type'], 'set') === 0): {
					$option_list = array();
					$field['type'] = 'set';
					foreach (explode(',', trim(substr($field_desc['Type'], 3), '()')) as $option_name) {
						$option_name = trim($option_name, '\'');
						$option_list[$option_name] = $option_name;
					}
					$field['option_list'] = $option_list;
					break;
				}
				case (stripos($field_desc['Type'], 'enum') === 0): {
					$option_list = array();
					$field['type'] = 'enum';
					foreach (explode(',', trim(substr($field_desc['Type'], 4), '()')) as $option_name) {
						$option_name = trim($option_name, '\'');
						$option_list[$option_name] = $option_name;
					}
					$field['option_list'] = $option_list;
					break;
				}
				default: {
					e("\nUNSUPPORTED MYSQL COLUMN TYPE: ".$field_desc['Type']."\n");
				}
			}

			$this->field_list[$field_name] = $field;
		}
	}



	public function search($row) {
		$query = new XA_Query($this->table_name);
		$row = $this->filter($row);

		foreach ($row as $field_name => $value) {
			$query->conditions[$field_name] = "$field_name = '".xa_db::esc($value)."'";
		}

		return xa::query($query)->all();
	}



	public function find($row) {
		if (!is_array($row) && count($this->primary_key_list) === 1) {
			$row = array($this->primary_key_list[0] => $row);
		}

		$query = new XA_Query($this->table_name);
		$query->limit = 1;
		$row = $this->filter($row);

		foreach ($row as $field_name => $value) {
			if ($value === null) {
				$query->conditions[$field_name] = "$field_name IS NULL";
			} else {
				$query->conditions[$field_name] = "$field_name = '".xa_db::esc($value)."'";
			}
		}

		return xa::query($query)->row();
	}



	public function exists($row, $pk_only = false) {
		if (!is_array($row) && count($this->primary_key_list) === 1) {
			$row = array($this->primary_key_list[0] => $row);
		}

		$row = $this->filter($row, $pk_only);
		$query = new XA_Query($this->table_name);
		$query->fields = array('exists' => '1');
		$query->limit = 1;
		$row = $this->filter($row);

		foreach ($row as $field_name => $value) {
			$query->conditions[$field_name] = "$field_name = '".xa_db::esc($value)."'";
		}

		return (bool)xa::query($query)->row();
	}



	public function count($row) {
		$query = new XA_Query($this->table_name);
		$query->fields = array('count' => "COUNT(1)");
		$row = $this->filter($row);

		foreach ($row as $field_name => $value) {
			$query->conditions[$field_name] = "$field_name = '".xa_db::esc($value)."'";
		}

		return intval(xa::query($query)->val());
	}



	public function prepare($value, $field_name) {
		$field = $this->field_list[$field_name];

		if ($field['type'] === 'int') {
			if ($field['null'] && $field['unsigned'] && empty($value)) {
				return 'NULL';
			}

			return intval($value);
		}

		if ($field['type'] === 'float') {
			if ($field['null'] && !xa_in::float($value)) {
				return 'NULL';
			}

			return $value;
		}

		if ($field['null'] && (($value === '') || is_null($value))) {
				return 'NULL';
		}

		return "'".xa_db::esc($value)."'";
	}



	public function insert($row) {
		$row = $this->filter($row);

		if (!empty($this->auto_increment_field)) {
			unset($row[$this->auto_increment_field]);
		}

		$this->error_list = $this->error_list + $this->validate($row);

		if ($this->error_list) {
			return false;
		} else {
			if (empty($this->auto_increment_field)) {
				foreach ($this->primary_key_list as $pk_name) {
					if (empty($row[$pk_name])) {
						$this->error_list[$pk_name]['pk_not_set'] = true;
					}
				}
			}

			$value_list = array();

			if (!$this->error_list) {
				foreach ($row as $field_name => $value) {
					$value_list[] = $this->prepare($value, $field_name);
				}

				return xa::query("INSERT INTO `".$this->table_name."` (`".implode('`, `', array_keys($row))."`) VALUES (".implode(', ', $value_list).")");
			}

			return false;
		}
	}



	public function update($row) {
		$row = $this->filter($row);
		$this->error_list = $this->error_list + $this->validate($row);

		if ($this->error_list) {
			return false;
		}

		$condition_list =
		$value_list = array();

		foreach ($this->primary_key_list as $pk_name) {
			if (empty($row[$pk_name])) {
				$this->error_list[$pk_name]['pk_not_set'] = true;
			}

			$condition_list[] = "`".$pk_name."` = ".$this->prepare($row[$pk_name], $pk_name);
			unset($row[$pk_name]);
		}

		if ($this->error_list) {
			return false;
		}

		foreach ($row as $field_name => $value) {
			$value_list[] = "`".$field_name."` = ".$this->prepare($value, $field_name);
		}

		if ($value_list) {
			return xa::query("UPDATE `".$this->table_name."` SET ".implode(', ', $value_list)." WHERE ".implode(' AND ', $condition_list)." LIMIT 1");
		} else {
			return true;
		}
	}



	public function delete($row) {
		if (!is_array($row) && count($this->primary_key_list) === 1) {
			$row = array($this->primary_key_list[0] => $row);
		}

		$row = $this->filter($row);
		$this->error_list = $this->error_list + $this->validate($row);
		if ($this->error_list) {
			return false;
		} else {
			$condition_list = array();

			foreach ($row as $field_name => $val) {
				$condition_list[] = "`".$field_name."` = ".$this->prepare($val, $field_name);
			}

			if (!$this->error_list) {
				return xa::query("DELETE FROM `".$this->table_name."` WHERE ".implode(' AND ', $condition_list));
			} else {
				e($this->error_list);
			}

			return false;
		}
	}



	public function validate($row, $skip_pk = false) {
		if ($skip_pk) {
			foreach ($this->primary_key_list as $pk) {
				unset($row[$pk]);
			}
		}
		$row = $this->filter($row);
		$error_list = array();

		foreach ($row as $field_name => $value) {
			$field = $this->field_list[$field_name];

			switch (true) {

				case ($field['type'] === 'int'): {
					if ($value === '') {
						if ($field['null']) {
							break;
						}

						$error_list[$field_name]['empty'] = true;
						break;
					}

					if (
						$field['unsigned']
						&&
						!xa_in::nat($value)
						&&
						$value !== '0'
						&&
						$value !== 0
					) {
						if ($field['null'] && ($value === '' || $value === null)) {
							break;
						}

						$error_list[$field_name]['not_natural'] = true;
					} elseif ((xa_in::int($value) === false) && !$field['null']) {
						if ($field['null'] && ($value === '' || $value === null)) {
							break;
						}

						$error_list[$field_name]['not_integer'] = true;
					}

					break;
				}

				case ($field['type'] === 'float'): {
					if (xa_in::float($value) === false && !$field['null']) {
						$error_list[$field_name]['not_float'] = true;
					}

					break;
				}

				case ($field['type'] === 'datetime'): {
					if (!xa_in::datetime($value)) {
						$error_list[$field_name]['not_datetime'] = true;
					}

					break;
				}

				case ($field['type'] === 'date'): {
					if (empty($value) && $field['null']) {
						break;
					}

					if (!xa_in::date($value)) {
						$error_list[$field_name]['not_date'] = true;
					}

					break;
				}

				/*
				case ($field['type'] === 'enum'): {
					if (isset($row[$field_name])) {
						if (in_array($row[$field_name], array_keys($field['option_list']))) {
								$record[$field_name] = $row[$field_name];
						}
					}
					break;
				}
				case ($field['type'] === 'set'): {
					$row_value_list = array();
					$option_list = array_keys($field['option_list']);
					foreach ($row as $row_value_name => $row_value) {
						if ((substr($row_value_name, 0, strlen($field_name)) === $field_name) && in_array(substr($row_value_name, strlen($field_name) + 1), $option_list)) {
							$row_value_list[] = substr($row_value_name, strlen($field_name) + 1);
						}
					}
					$record[$field_name] = implode(',', $row_value_list);
					break;
				}
				*/
			}
		}

		return $error_list;
	}



	public function getPrimaryKeyList() {
		return $this->primary_key_list;
	}



	public function getFieldList() {
		return $this->field_list;
	}
}
